﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Configuration;
using WooYang.DataBase;
using System.Data;
using System.ComponentModel;
using System.Collections;
using System.Text.RegularExpressions;

namespace WooYang.Data.DataBase
{
    /// <summary>
    /// WooYang
    /// </summary>
    public class DataBaseInstance : IDisposable
    {
        private readonly DbConnection _connection;
        private static DbProviderFactory _factory = DbProviderFactories.GetFactory("System.Data.SqlClient");//ConfigurationManager.ConnectionStrings["ecard"].ProviderName); 
        private DbTransaction _transaction;

        public DataBaseInstance(DbConnection conn)
        {
            _connection = conn;
            _connection.Open();
        }

        public bool Insert(object item, string tableName)
        {
            var command = SqlBuildHelper.InsertBuild(item,_factory);
            command.Connection = _connection;
            if (_transaction != null)
                command.Transaction = _transaction;
            int i = command.ExecuteNonQuery();
            if (i > 0)
                return true;
            return false;
        }
        public bool Update(object item, string tableName)
        {
            var command = SqlBuildHelper.UpdateBuild(item, tableName, _factory);
            command.Connection = _connection;
            if (_transaction != null)
                command.Transaction = _transaction;
            int i = command.ExecuteNonQuery();
            if (i > 0)
                return true;
            return false;
        }
        public int Delete(object item, string tableName)
        {
            var command = SqlBuildHelper.DeleteBuild(item, tableName, _factory);
            command.Connection = _connection;
            if (_transaction != null)
                command.Transaction = _transaction;
            return command.ExecuteNonQuery();
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="parameterObject"></param>
        /// <param name="skip">起始行</param>
        /// <param name="take">结束行</param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public DataTable Table(string strSql, object parameterObject, int skip, int take, string orderBy)
        {
            orderBy = orderBy.ToLower();
            bool asc = true;
            if (orderBy.EndsWith("desc"))
            {
                orderBy = orderBy.Substring(0, orderBy.Length - 4);
                asc = false;
            }
            while (orderBy.IndexOf(" ") >= 0)
            {
                orderBy = orderBy.Trim();
            }
            strSql = "select tt.* from (select row_number()over(order by {0}) rownum, * from(" +strSql + ") t) tt where rownum between {1} and {2}";
            strSql = string.Format(strSql, orderBy + (asc ? "" : " desc"), skip + 1, skip + take);
            return Table(strSql, parameterObject);
        }

        public DataTable Table(string strSql, object parameterObject)
        {
           
            var dt = new DataTable();
            DbDataAdapter adapter = _factory.CreateDataAdapter();
            var parameters = BuildParameters(parameterObject, ref strSql);
            DbCommand command = SqlBuildHelper.SelectBuild(strSql, parameters, _factory);
            if (_transaction != null)
                command.Transaction = _transaction;
            adapter.SelectCommand = command;
            adapter.Fill(dt);
            return dt;
        }
        private IEnumerable<DbParameter> BuildParameters(object parameterObject, ref string sql)
        {
            var parameters = new List<DbParameter>();
            if (parameterObject == null)
                return parameters;

            var dict = parameterObject as IDictionary;
            if (dict != null)
            {
                foreach (object key in dict.Keys)
                {
                    object value = dict[key];
                    string parameterName = key.ToString();
                    BuildParameter(parameterName, value, parameters, ref sql);
                }
                return parameters;
            }
            Type type = parameterObject.GetType();
            foreach (System.ComponentModel.PropertyDescriptor property in TypeDescriptor.GetProperties(type))
            {
                BuildParameter(property.Name, property.GetValue(parameterObject), parameters, ref sql);
            }
            return parameters;
        }

        private void BuildParameter(string parameterName, object value, List<DbParameter> parameters, ref string sql)
        {
            var items = value as IEnumerable;
            if (items != null && items.GetType() != typeof(string) && items.GetType() != typeof(byte[]))
            {
                value = null;
                var names = new List<string>();
                int index = 1;
                foreach (object obj in items)
                {
                    string tmpName = parameterName + "_" + index;
                    names.Add(tmpName);
                    var p = _factory.CreateParameter();
                    p.ParameterName = tmpName;
                    p.Value = value;
                    parameters.Add(p);
                    index++;
                    value = true;
                }
                if (value != null)
                {
                    var regex = new Regex(@"\(\s*@" + parameterName + @"\s*\)", RegexOptions.IgnoreCase);
                    sql = regex.Replace(sql, @"(" + string.Join(",", names.Select(x => "@" + x).ToArray()) + ")");
                }
                else // value = null, no items in here
                {
                    var regex = new Regex(@"\(\s*@" + parameterName + @"\s*\)", RegexOptions.IgnoreCase);
                    sql = regex.Replace(sql, @"(null)");
                    value = true;
                }
            }
            var pp = _factory.CreateParameter();
            pp.Value = value;
            pp.ParameterName = parameterName;
            parameters.Add(pp);
        }

        public object ExecuteScalar(string strSql, object parameterObject)
        {
            IEnumerable<DbParameter> parameters = BuildParameters(parameterObject, ref strSql);
            DbCommand command = _factory.CreateCommand();
            command.Connection = _connection;
            command.CommandText = strSql;


            if (_transaction != null)
                command.Transaction = _transaction;
            foreach (DbParameter dbParameter in parameters)
            {
                command.Parameters.Add(dbParameter);
            }
            return command.ExecuteScalar();
        }

        public long Count(string strSql, object parameterObject)
        {
            IEnumerable<DbParameter> parameters = BuildParameters(parameterObject, ref strSql);
            DbCommand command = _factory.CreateCommand();
            command.CommandText = strSql;
            command.Connection = _connection;
            if (_transaction != null)
                command.Transaction = _transaction;
            foreach (DbParameter dbParameter in parameters)
            {
                command.Parameters.Add(dbParameter);
            }
            return Convert.ToInt64(command.ExecuteScalar() ?? 0);
        }
        /// <summary>
        /// 表的行数
        /// </summary>
        /// <typeparam name="T">T的类型就是表名</typeparam>
        /// <param name="strSql"></param>
        /// <param name="parameterObject"></param>
        /// <returns></returns>
        public long Count<T>()
        {
            string strSql = "select count(*) from {0} where 1=1 ";
            Type type = typeof(T);
            strSql = string.Format(strSql, type.Name);
            DbCommand command = _factory.CreateCommand();
            command.CommandText = strSql;
            command.Connection = _connection;
            if (_transaction != null)
                command.Transaction = _transaction;
            return Convert.ToInt64(command.ExecuteScalar() ?? 0);
        }

        public IEnumerable<T> Query<T>(string strSql, object parameterObject, int skip, int take, string orderBy)
        {

            orderBy = orderBy.ToLower();
            bool asc = true;
            if (orderBy.EndsWith("desc"))
            {
                orderBy = orderBy.Substring(0, orderBy.Length - 4);
                asc = false;
            }
            while (orderBy.IndexOf(" ") >= 0)
            {
                orderBy = orderBy.Trim();
            }
            strSql =
                "select  tt.* from (select row_number()over(order by {0}) rownum, * from(" +strSql + ") t) tt where rownum between {1} and {2}";
            strSql = string.Format(strSql, orderBy + (asc ? "" : " desc"), skip + 1, skip + take);
            return Query<T>(strSql, parameterObject);
        }

        public IEnumerable<T> Query<T>(string strSql, object parameterObject)
        {
            IEnumerable<DbParameter> parameters = BuildParameters(parameterObject, ref strSql);
            DbCommand command = _factory.CreateCommand();
            command.CommandText = strSql;
            command.Connection = _connection;
            if (_transaction != null)
                command.Transaction = _transaction;

            foreach (DbParameter dbParameter in parameters)
            {
                command.Parameters.Add(dbParameter);
            }
           
            using (DbDataReader reader = command.ExecuteReader())
            {
                
                while (reader.Read())
                {
                    Type type = typeof(T);
                 
                    yield return (T)ReaderVSModel.ToModel<T>(reader, Activator.CreateInstance(type));
                }
            }
        }


        public T GetById<T>(string tableName, object id)
        {
            DbCommand command = SqlBuildHelper.GetByIdBuild(typeof(T), tableName, id, _factory);
            command.Connection = _connection;
            if (_transaction != null)
                command.Transaction = _transaction;
            using (DbDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Type type = typeof(T);
                    return ReaderVSModel.ToModel<T>(reader, Activator.CreateInstance(type));
                }
            }
            return default(T);
        }

        public DbTransaction BeginTransaction()
        {
            _transaction = _connection.BeginTransaction();
            return _transaction;
        }

        public void Commit()
        {
            _transaction.Commit();
            _transaction = null;
        }

        public void Dispose()
        {
            _connection.Dispose();
            _transaction.Dispose();
            using (_connection)
            {
                using (_transaction)
                {
                }
            }
        }
    }
}
